package dao.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.RuteBean;
import bean.loader.RuteLoader;
import cas.Identity;
import dao.DAOFactory;
import dao.ResourceManager;

public class RuteDao {
	private DAOFactory factory;
	
	public RuteDao(DAOFactory factory) {
		super();
		this.factory = factory;
	}

	public List<RuteBean> getRuteWhereNamaRute(String namaRute) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM RUTE WHERE NAMA_RUTE = ? ORDER BY URUTAN ASC");
			ps.setString(1, namaRute);
			rs = ps.executeQuery();
			return new RuteLoader().loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	
	public String[] getListNamaRute() throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT DISTINCT NAMA_RUTE FROM RUTE ORDER BY NAMA_RUTE ASC");
			rs = ps.executeQuery();
			List<String> namaRute = new ArrayList<String>();
			while(rs.next()){
				namaRute.add(rs.getString("NAMA_RUTE"));
			}
			return namaRute.toArray(new String[0]);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	public Integer[] getSameCoordsIdWhere(Identity myTrain, Identity anotherTrain) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT ID " +
					"FROM " +
						"(SELECT ID, URUTAN " +
						"FROM RUTE " +
						"WHERE NAMA_RUTE = ? AND URUTAN >= ?) " +
					"WHERE ID IN(SELECT ID FROM RUTE WHERE NAMA_RUTE = ? AND URUTAN >= ? ) " +
					"ORDER BY URUTAN ASC");
			ps.setString(1, myTrain.getRute().get(0).getNamaRute());
			ps.setInt(2, myTrain.getUrutan());
			ps.setString(3, anotherTrain.getRute().get(0).getNamaRute());
			ps.setInt(4, anotherTrain.getUrutan());
			rs = ps.executeQuery();
			List<Integer> result = new ArrayList<Integer>();
			while(rs.next()){
				result.add(rs.getInt(1));
			}
			return result.toArray(new Integer[0]);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
		
	}
}
